﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using System.Linq.Expressions;
using System.Data.Linq.SqlClient;

namespace Datalayer
{
    internal abstract class DbExecQueryBase : IQueryMgr
    {
        private bool _disposed;
        private KonPlonDBDataContext db;

        protected DbExecQueryBase(KonPlonDBDataContext db)
        {
            this.db = db;
        }
        #region User
        public List<User> UR_GetAllUser()
        {
            var q = from p in db.Users
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<User>();
        }

        public List<User> UR_GetUserByRoleID(Guid roleid)
        {
            var q = from p in db.Users
                    where p.IsDeleted == false && p.RoleID == roleid
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<User>();
        }

        public List<User> UR_GetUserByDepartID(Guid departId)
        {
            var q = from p in db.Users
                    where p.IsDeleted == false && p.DepartID == departId
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<User>();
        }

        public List<User> UR_GetUserByStatusID(Guid StatusID)
        {
            var q = from p in db.Users
                    where p.IsDeleted == false && p.StatusID == StatusID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<User>();
        }

        public User UR_GetUserByID(Guid pUserID)
        {
            
            var q = from p in db.Users
                    where p.UserID == pUserID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        
        public User UR_GetUserByUserName(String pUserName)
        {

            var q = from p in db.Users
                    where p.UserName == pUserName && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public User UR_GetUserByUsernameAndPassword(string pUsername, string pPassword)
        {

            var q = from p in db.Users
                    where p.UserName == pUsername && p.Password == pPassword && p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
                return q.ToList().FirstOrDefault();
            else
                return null;
        }

        public User UR_GetUserByUsernameAndEmail(string pUsername, string pEmail)
        {

            var q = from p in db.Users
                    where p.UserName == pUsername && p.Mail == pEmail && p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
                return q.ToList().FirstOrDefault();
            else
                return null;
        }

        public void UR_NewUser(User pUser)
        {
            db.Users.InsertOnSubmit(pUser);
            db.SubmitChanges();
        }

        public void UR_EditUser(User pUser)
        {
            var q = from p in db.Users
                    where p.UserID == pUser.UserID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                User obj = q.FirstOrDefault();
                obj.UserName = pUser.UserName;
                obj.Role = db.Roles.Single(c => c.RoleID == pUser.RoleID);
                obj.Password = pUser.Password;
                obj.FullName = pUser.FullName;
                obj.CreateDate = pUser.CreateDate;
                obj.IsDeleted = pUser.IsDeleted;
                obj.IsCanBo = pUser.IsCanBo;
                obj.Identification = db.Identifications.Single(c => c.IdentificationID == pUser.IdentificationID);
                obj.IdentificationNumber = pUser.IdentificationNumber;
                obj.Mail = pUser.Mail;
                obj.Phone = pUser.Phone;
                obj.Address = pUser.Address;
                obj.Sex = pUser.Sex;
                obj.Status = db.Status.Single(c => c.StatusID == pUser.StatusID);
                
                db.SubmitChanges();
            }
        }

        public void UR_DelOrResUser(Guid pUserID)
        {
            
            var q = from p in db.Users
                    where p.UserID == pUserID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                User obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region Role
        public List<Role> Ro_GetAllRole()
        {
            var q = from p in db.Roles
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Role>();
        }

        public Role Ro_GetRoleByID(Guid pRoleID)
        {
            
            var q = from p in db.Roles
                    where p.RoleID == pRoleID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region Depart
        public List<Depart> Dep_GetAllDepart()
        {
            var q = from p in db.Departs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Depart>();
        }

        public Depart Dep_GetDepartByID(Guid pDepartID)
        {

            var q = from p in db.Departs
                    where p.DepartID == pDepartID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Dep_NewDepart(Depart pDepart)
        {
            db.Departs.InsertOnSubmit(pDepart);
            db.SubmitChanges();
        }

        public void Dep_EditDepart(Depart pDepart)
        {
            var q = from p in db.Departs
                    where p.DepartID == pDepart.DepartID && p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Depart obj = q.FirstOrDefault();

                obj.DepartName = pDepart.DepartName;
                obj.IsDeleted = false;
                obj.Links = pDepart.Links;
                db.SubmitChanges();
            }
        }

        public void Dep_DelOrResDepart(Guid pNewID)
        {
            var q = from p in db.News
                    where p.NewID == pNewID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                New obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region Status
        public List<Status> Sta_GetAllStatus()
        {
            var q = from p in db.Status
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Status>();
        }

        public Status Sta_GetStatusByID(Guid pStatusID)
        {

            var q = from p in db.Status
                    where p.StatusID == pStatusID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region Identification
        public List<Identification> Iden_GetAllIdentification()
        {
            var q = from p in db.Identifications
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Identification>();
        }

        public Identification Iden_GetIdentificationByID(Guid pIdenID)
        {

            var q = from p in db.Identifications
                    where p.IdentificationID == pIdenID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region News
        public List<New> News_GetAllNew()
        {
            var q = from p in db.News
                    where p.IsDeleted == false
                    orderby p.CreateDate descending 
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<New>();
        }
        public List<New> News_GetAllNew_search(String strSearch)
        {
            var q = from p in db.News
                    where p.IsDeleted == false && (p.NewTitle.Contains(@strSearch) || p.NewIntro.Contains(@strSearch))
                    orderby p.CreateDate descending
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<New>();
        }

        public List<New> News_GetNewByCatID(Guid catid)
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.CateID == catid
                    orderby p.CreateDate descending 
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<New>();
        }

        public List<New> News_GetNewByCreateBy(Guid CreateBy)
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.CreateBy == CreateBy
                    orderby p.CreateDate descending 
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<New>();
        }

        public List<New> News_GetNewByModifyBy(Guid ModifyBy)
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.ModifyBy == ModifyBy
                    orderby p.CreateDate descending 
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<New>();
        }

        public New News_GetNewByID(Guid pNewID)
        {

            var q = from p in db.News
                    where p.NewID == pNewID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public List<New> News_GetHitTop10()
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.IsShow == true
                    orderby p.Hit descending 
                    select p;
            if (q.Count() > 0)
                return q.Take(10).ToList();
            else
                return new List<New>();
        }

        public List<New> News_GetNewsTop10()
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.IsShow == true
                    orderby p.CreateDate descending 
                    select p;
            if (q.Count() > 0)
                return q.Take(10).ToList();
            else
                return new List<New>();
        }
        
        public List<New> News_GetHitTop5()
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.IsShow == true
                    orderby p.Hit descending
                    select p;
            if (q.Count() > 0)
                return q.Take(5).ToList();
            else
                return new List<New>();
        }

        public List<New> News_GetNewsTop5()
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.IsShow == true
                    orderby p.CreateDate descending
                    select p;
            if (q.Count() > 0)
                return q.Take(5).ToList();
            else
                return new List<New>();
        }

        public List<New> News_GetNewsHot5()
        {
            var q = from p in db.News
                    where p.IsDeleted == false && p.IsHot == true
                    orderby p.CreateDate descending
                    select p;
            if (q.Count() > 0)
                return q.Take(5).ToList();
            else
                return new List<New>();
        }
        
        public void News_NewNew(New pNew)
        {
            db.News.InsertOnSubmit(pNew);
            db.SubmitChanges();
        }

        public void News_EditNew(New pNew)
        {
            var q = from p in db.News
                    where p.NewID == pNew.NewID && p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                New obj = q.FirstOrDefault();
                
                obj.NewTitle = pNew.NewTitle;
                obj.NewTitle_en = pNew.NewTitle_en;
                obj.Category = db.Categories.Single(c => c.CateID == pNew.CateID);
                obj.User = db.Users.Single(c => c.UserID == pNew.CreateBy);
                obj.CreateDate = pNew.CreateDate;
                obj.IsDeleted = pNew.IsDeleted;
                obj.Hit = pNew.Hit;
                obj.Image = pNew.Image;
                obj.IsShow = pNew.IsShow;
                obj.User1 = db.Users.Single(c => c.UserID == pNew.ModifyBy);
                obj.ModifyDate = pNew.ModifyDate;
                obj.NewContent = pNew.NewContent;
                obj.NewContent_en = pNew.NewContent_en;
                obj.NewIntro = pNew.NewIntro;
                obj.NewIntro_en = pNew.NewIntro_en;
                obj.Source = pNew.Source;

                db.SubmitChanges();
            }
        }

        public void News_DelOrResNew(Guid pNewID)
        {

            var q = from p in db.News
                    where p.NewID == pNewID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                New obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }

        public void News_Hit(Guid pNewID)
        {

            var q = from p in db.News
                    where p.NewID == pNewID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                New obj = q.FirstOrDefault();
                if (obj.Hit != null)
                    obj.Hit = obj.Hit + 1;
                else
                    obj.Hit = 1;
                db.SubmitChanges();
            }
        }
        #endregion

        #region Cate
        public List<Category> Ca_GetAllCate()
        {
            var q = from p in db.Categories
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Category>();
        }

        public List<Category> Ca_GetAllCate_NotParent()
        {
            var q = from p in db.Categories
                    where p.IsDeleted == false && p.CateParentID == null
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Category>();
        }

        public Category Ca_GetCateByID(Guid pCateID)
        {

            var q = from p in db.Categories
                    where p.CateID == pCateID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public List<Category> Ca_GetByCateParentID(Guid pCateParentID)
        {
            var q = from p in db.Categories
                    where p.IsDeleted == false && p.CateParentID == pCateParentID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Category>();
        }

        public void Ca_NewCate(Category pCategory)
        {
            db.Categories.InsertOnSubmit(pCategory);
            db.SubmitChanges();
        }

        public void Ca_EditCategory(Category pCategory)
        {
            var q = from p in db.Categories
                    where p.CateID == pCategory.CateID &&
                    p.IsDeleted == false
                    select p;
            
            if (q.Count() > 0)
            {
                Category obj = q.FirstOrDefault();
                obj.CategoryName = pCategory.CategoryName;
                obj.STT = pCategory.STT;
                obj.Links = pCategory.Links;
                obj.Menus = db.Menus.Single(c => c.MenuID == pCategory.MenuID);
                obj.IsDeleted = pCategory.IsDeleted;

                db.SubmitChanges();
            }
        }

        public void Ca_DelOrResCategory(Guid pCategoryID)
        {

            var q = from p in db.Categories
                    where p.CateID == pCategoryID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Category obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        
        public void Ca_NullParent(Category pCategory)
        {

            var q = from p in db.Categories
                    where p.CateID == pCategory.CateID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Category obj = q.FirstOrDefault();
                obj.CateParentID = null;
                db.SubmitChanges();
            }
        }
        public void Ca_HaveParent(Category pCategory)
        {

            var q = from p in db.Categories
                    where p.CateID == pCategory.CateID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Category obj = q.FirstOrDefault();
                obj.Category1 = db.Categories.Single(c => c.CateID == pCategory.CateParentID);
                db.SubmitChanges();
            }
        }
        #endregion

        #region Menu
        public List<Menus> Menu_GetAllMenu()
        {
            var q = from p in db.Menus
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Menus>();
        }
        public Menus Menu_GetMenuByID(Guid pMenuID)
        {
            var q = from p in db.Menus
                    where p.MenuID == pMenuID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region Webconfig

        public WebConfig wcf_GetWebconfByID()
        {

            var q = from p in db.WebConfigs
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void wcf_EditCategory(WebConfig pWebconfig)
        {
            var q = from p in db.WebConfigs
                    where p.ConfigID == pWebconfig.ConfigID
                    select p;

            if (q.Count() > 0)
            {
                WebConfig obj = q.FirstOrDefault();
                obj.WebFooter = pWebconfig.WebFooter;
                obj.WebTitle = pWebconfig.WebTitle;
                obj.WebNotes = pWebconfig.WebNotes;
                db.SubmitChanges();
            }
        }
        
        #endregion

        #region Comment
        public List<Comment> CM_GetAllComment()
        {
            var q = from p in db.Comments
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Comment>();
        }

        public List<Comment> CM_GetAllCommentByChecked()
        {
            var q = from p in db.Comments
                    where p.IsDeleted == false && p.Active==true
                    orderby p.CreatedDate
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Comment>();
        }

        public List<Comment> CM_GetCommentByCheckedby(Guid Checkedby)
        {
            var q = from p in db.Comments
                    where p.IsDeleted == false && p.CheckedBy == Checkedby
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Comment>();
        }

        public Comment CM_GetCommentByID(Guid pCommentID)
        {
            
            var q = from p in db.Comments
                    where p.ComID == pCommentID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void CM_NewComment(Comment pComment)
        {
            db.Comments.InsertOnSubmit(pComment);
            db.SubmitChanges();
        }

        public void CM_EditComment(Comment pComment)
        {
            var q = from p in db.Comments
                    where p.ComID == pComment.ComID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Comment obj = q.FirstOrDefault();

                obj.Active = pComment.Active;
                obj.CheckedDate = pComment.CheckedDate;
                obj.ComContent = pComment.ComContent;
                obj.ComReply = pComment.ComReply;
                obj.ComTitle = pComment.ComTitle;
                obj.FullName = pComment.FullName;
                obj.IsDeleted = pComment.IsDeleted;
                obj.Email = pComment.Email;
                obj.Phone = pComment.Phone;
                obj.Address = pComment.Address;
                obj.User = db.Users.Single(c => c.UserID == pComment.CheckedBy);

                db.SubmitChanges();
            }
        }

        public void CM_DelOrResComment(Guid ComID)
        {

            var q = from p in db.Comments
                    where p.ComID == ComID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Comment obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region FileUpload
        public List<FileUpload> FU_GetAllFileUpload()
        {
            var q = from p in db.FileUploads
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<FileUpload>();
        }

        public List<FileUpload> FU_GetFileUploadByCommentID(Guid CommentID)
        {
            var q = from p in db.FileUploads
                    where p.IsDeleted == false && p.CommentID == CommentID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<FileUpload>();
        }

        public List<FileUpload> FU_GetFileUploadByDocumentID(Guid DocumentID)
        {
            var q = from p in db.FileUploads
                    where p.IsDeleted == false && p.DocumentID == DocumentID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<FileUpload>();
        }

        public FileUpload FU_GetFileUploadByID(Guid pFileUploadID)
        {

            var q = from p in db.FileUploads
                    where p.FileID == pFileUploadID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void FU_NewFileUpload(FileUpload pFileUpload)
        {
            db.FileUploads.InsertOnSubmit(pFileUpload);
            db.SubmitChanges();
        }

        public void FU_EditFileUpload(FileUpload pFileUpload)
        {
            var q = from p in db.FileUploads
                    where p.FileID == pFileUpload.FileID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                FileUpload obj = q.FirstOrDefault();

                obj.FileName = pFileUpload.FileName;
                obj.IsDeleted = pFileUpload.IsDeleted;
                obj.Links = pFileUpload.Links;
                obj.Comment = db.Comments.Single(c => c.ComID == pFileUpload.CommentID);
                obj.Document = db.Documents.Single(c => c.DocID == pFileUpload.DocumentID);

                db.SubmitChanges();
            }
        }

        public void FU_DelOrResFileUpload(Guid FUID)
        {

            var q = from p in db.FileUploads
                    where p.FileID == FUID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                FileUpload obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region Document
        public List<Document> Doc_GetAllDocument()
        {
            var q = from p in db.Documents
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Document>();
        }

        public List<Document> Doc_GetDocumentByTypeDocID(Guid TypeDocID)
        {
            var q = from p in db.Documents
                    where p.IsDeleted == false && p.LinhVuc == TypeDocID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<Document>();
        }

        public Document Doc_GetDocumentByID(Guid pDocumentID)
        {

            var q = from p in db.Documents
                    where p.DocID == pDocumentID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Doc_NewDocument(Document pDocument)
        {
            db.Documents.InsertOnSubmit(pDocument);
            db.SubmitChanges();
        }

        public void Doc_EditDocument(Document pDocument)
        {
            var q = from p in db.Documents
                    where p.DocID == pDocument.DocID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Document obj = q.FirstOrDefault();

                obj.CoQuanBanHanh = pDocument.CoQuanBanHanh;
                obj.IsDeleted = pDocument.IsDeleted;
                obj.NgayBanHanh = pDocument.NgayBanHanh;
                obj.NguoiKy = pDocument.NguoiKy;
                obj.PhanLoai = pDocument.PhanLoai;
                obj.SoKyHieu = pDocument.SoKyHieu;
                obj.TenVanBan = pDocument.TenVanBan;
                obj.TrichYeu = pDocument.TrichYeu;
                obj.TypeDocument = db.TypeDocuments.Single(c => c.TypeDocID == pDocument.LinhVuc);
                
                db.SubmitChanges();
            }
        }

        public void Doc_DelOrResDocument(Guid DocID)
        {

            var q = from p in db.Documents
                    where p.DocID == DocID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                Document obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region Dispose

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this._disposed)
            {
                if (disposing)
                {
                    if (db != null)
                    {
                        db.Dispose();
                    }
                }
                _disposed = true;
            }
        }

        protected bool Disposed
        {
            get
            {
                return _disposed;
            }
            set
            {
                _disposed = value;
            }
        }
        #endregion

        protected KonPlonDBDataContext DbDataContext
        {
            get
            {
                return db;
            }
        }
    }
}

